/**
 * author wen
 * cunyu
 * email wjd_13@hotmail.com
 */
package com.cunyu.util;

import cn.tom.kit.StringUtil;
import com.cunyu.bean.OrderPool;
import com.cunyu.service.OrderPoolService;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * excel 处理工具
 * @author win
 * 2019/05/06-13:40
 */
public class ExcelReader {

    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelReader.class) ;

    public static List<Object[]> orderPoolFile(MultipartFile file,
                                               Map<String, Map<String, Object>> tagNameMap, OrderPoolService orderPoolService) {
        List<Object[]> list=new ArrayList<Object[]>();
        try {
            HSSFWorkbook excel = new HSSFWorkbook(file.getInputStream()) ;
            HSSFSheet sheet = null;
            List<Long> fileTels = new ArrayList<>() ;
            for (int s = 0; s < excel.getNumberOfSheets(); s++) {
                sheet = excel.getSheetAt(s);
                // 定义 row、cell
                /*List<String> title = new ArrayList<String>();
                HSSFRow row = sheet.getRow(0); // 表头
                for (int t = row.getFirstCellNum(); t < row.getPhysicalNumberOfCells(); t++) {
                    title.add(row.getCell(t).toString());
                }*/
                HSSFRow row = null ;
                // 循环输出表格中的内容
                for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
                    row = sheet.getRow(i);
                    Object[] pool = new Object[26] ;
                    for (int j = row.getFirstCellNum(); j <row.getPhysicalNumberOfCells(); j++) {
                        row.getCell(j).setCellType(Cell.CELL_TYPE_STRING) ;
                        String info = row.getCell(j).toString().trim() ;
                        pool[j] = getInfo(tagNameMap,j,info) ;
                    }
                    // 判断本文件是否有重复手机号
                    if(fileTels.contains(pool[3])){
                        LOGGER.info(pool[3] +" same tel .");
                        continue;
                    }
                    // 判断是否有和数据库重复的手机号
                    int size = orderPoolService.telCount(pool[3]) ;
                    if(size > 0){
                        LOGGER.info(pool[3] +" has imported "+size);
                        continue;
                    }
                    fileTels.add((Long) pool[3]);
                    //1: // 年龄
                    int age = (int)pool[1] ;
                    int mon = (int)pool[6] ;
                    if(age < 18 || age > 65){
                        LOGGER.info(pool[3] +" age not suit: "+age);
                        continue;
                    }

                    //6: // 贷款时限
                    if(mon <1 || mon > 36){
                        LOGGER.info(pool[3] +" month not suit: "+mon);
                        continue;
                    }

                    //pool[4] 设置完整名称
                    pool[25] = orderPoolService.cityCode(pool[4]) ;
                    calc(pool) ;
                    list.add(pool);
                }
            }
        } catch (Exception e) {
            LOGGER.error("read excel file error ",e);
        }
        return list;
    }

    private static void calc(Object[] pool) {
        int score = 0 ;
        if((int)pool[9] == 1){
            score += 1 ;
        }
        if((int)pool[10] == 1){
            score += 1 ;
        }
        if((int)pool[17] < 3){
            score += 1 ;
        }
        if((int)pool[16] < 3){
            score += 1 ;
        }
        if((int)pool[18] == 1){
            score += 1 ;
        }
        if((int)pool[11] == 1){
            score += 1 ;
        }
        pool[23] = score >=2 ?1:0;
        // 改变单子价格
        pool[24] = score >=2 ?33:26;
    }

    private static Object getInfo(Map<String, Map<String, Object>> tagNameMap,
                                  int j,String in) {
        Object info = null;
        switch (j){
            case 0: // 姓名
                info = in ;
                break ;
            case 1: // 年龄
                info = getint(in);
                break ;
            case 2: // 性别
                info = "女".equals(in)?0:1 ;
                break ;
            case 3: // 电话
                info = Long.valueOf(in) ;
                break ;
            case 4: // 城市
                info = in ;
                break ;
            case 5: // 贷款金额
                info = fromMapInfo(tagNameMap,"贷款金额",in) ;
                break ;
            case 6: // 贷款时限
                info = getint(in) ;
                break ;
            case 7:
                info = fromMapInfo(tagNameMap,"贷款用途",in);
                break ;
            case 8:
                info = fromMapInfo(tagNameMap,"户籍类型",in);
                break ;
            case 9://社保
                info = fromMapInfo(tagNameMap,"社保,公积金",in);
                break ;
            case 10://公积金
                info = fromMapInfo(tagNameMap,"社保,公积金",in);
                break ;
            case 11://职业类型
                info = fromMapInfo(tagNameMap,"职业类型",in);
                break ;
            case 12://月收入
                info = fromMapInfo(tagNameMap,"月收入",in);
                break ;
            case 13: //收入方式
                info = fromMapInfo(tagNameMap,"收入方式",in);
                break ;
            case 14://信用卡
                info = fromMapInfo(tagNameMap,"信用卡,微粒贷,寿险",in);
                break ;
            case 15://微粒贷
                info = fromMapInfo(tagNameMap,"信用卡,微粒贷,寿险",in);
                break ;
            case 16://房产
                info = fromMapInfo(tagNameMap,"房产",in);
                break ;
            case 17://车产
                info = fromMapInfo(tagNameMap,"车产",in);
                break ;
            case 18://寿险
                info = fromMapInfo(tagNameMap,"信用卡,微粒贷,寿险",in);
                break ;
            case 19://车辆抵押
                info = fromMapInfo(tagNameMap,"车辆抵押,房产抵押",in);
                break ;
            case 20://房产抵押
                info = fromMapInfo(tagNameMap,"车辆抵押,房产抵押",in);
                break ;
            case 21://芝麻分
                info = fromMapInfo(tagNameMap,"芝麻分",in);
                break ;
            case 22://征信
                info = fromMapInfo(tagNameMap,"征信",in);
                break ;
            default:
                info = null ;
                System.out.println("unknown pos :"+j);
                break ;
        }
        return info ;
    }

    private static Object fromMapInfo(Map<String, Map<String, Object>> tagNameMap,
                                      String name,String key) {
        Map a =  (Map) tagNameMap.get(name).get(key) ;
        return Integer.valueOf( a.get("value").toString() );
    }

    private static Integer getint(String in) {
        if(StringUtil.hasLength(in))
            return Integer.valueOf(in) ;
        return null ;
    }

}
